﻿using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using ZHDJ.Data.Models.Common;

namespace ZHDJ.Data.Models.Center
{
    public class PartyPhotoWallImage : EntityBaseOperation
    {
        #region 属性

        /// <summary>
        /// 图库ID
        /// </summary>

        public string PhotoWallID { get; set; }

        /// <summary>
        /// 文件ID
        /// </summary>
        public string FileID { get; set; }


        /// <summary>
        /// 图片描述
        /// </summary>
        public string Describe { get; set; }

        /// <summary>
        /// （2禁用4启用）
        /// </summary>
        public int Status { get; set; }

        /// <summary>
        /// 0
        /// </summary>
        public int OrderNo { get; set; }

        public virtual PartyPhotoWall PartyPhotoWall { get; set; }
        #endregion

        /// <summary>
        /// 获取指定留影墙包含的图片
        /// </summary>
        /// <param name="photoWallId"></param>
        /// <returns></returns>
        public static DataTable GetFileData(string photoWallId)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select wallimage.ID,Title,`Describe`,Url,wallimage.CreateTime from tblpartyphotowallimage wallimage
left join tblpartyfile file on wallimage.FileID=file.ID
where PhotoWallID=?PhotoWallID order by wallimage.CreateTime");

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("?PhotoWallID",MySqlDbType.VarChar)
            };
            parameters[0].Value = photoWallId;

            return MySqlHelper.ExecuteDataTable(sb.ToString(), parameters);
        }

        /// <summary>
        /// 获取首页留影墙轮播图片
        /// </summary>
        /// <param name="topCount"></param>
        /// <param name="thematic"></param>
        /// <returns></returns>
        public static DataTable GetPortalPhotoWallList(int topCount, string thematic = "")
        {
            string sql = @"select ID from tblpartyphotowall order by CreateTime desc LIMIT " + topCount + " ";
            DataTable dtPhotoWall = MySqlHelper.ExecuteDataTable(sql);
            DataTable dtPhotoWallImageTmp = new DataTable();
            DataTable dtPhotoWallImage = null;




            for (int i = 0; i < dtPhotoWall.Rows.Count; i++)
            {
                sql = @"select photo.ID,Url,`Describe` from tblpartyphotowallimage image
                    left join tblpartyfile file on file.ID=image.FileID
                    left join tblpartyphotowall photo on image.PhotoWallID=photo.ID
                    where PhotoWallID='" + Convert.ToString(dtPhotoWall.Rows[i]["ID"]) + "' order by image.CreateTime desc limit 0,1";
                dtPhotoWallImageTmp = MySqlHelper.ExecuteDataTable(sql);

                if (i == 0)
                {
                    dtPhotoWallImage = dtPhotoWallImageTmp.Copy();
                    dtPhotoWallImage.Clear();

                }
                if (dtPhotoWallImageTmp.Rows.Count <= 0)
                {
                    continue;
                }


                dtPhotoWallImage.Rows.Add(dtPhotoWallImageTmp.Rows[0].ItemArray);

            }

            return dtPhotoWallImage;
        }

        /// <summary>
        /// 获取组织中心留影墙轮播图片
        /// </summary>
        /// <param name="topCount"></param>
        /// <param name="thematic"></param>
        /// <returns></returns>
        public static DataTable GetBranchPhotoWallList(int topCount, string branchId, string thematic)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(@"select ID from tblpartyphotowall where 1=1");

            if (!string.IsNullOrEmpty(branchId))
            {
                sb.AppendFormat(" and BranchID in (select childbranchid from tblpartyorgbranchrelation where BranchID='{0}')", branchId);
            }
            if (!string.IsNullOrEmpty(thematic))
            {
                sb.AppendFormat(" and Thematic='{0}'", thematic);
            }
            sb.Append(" order by CreateTime desc");

            DataTable dtPhotoWall = MySqlHelper.ExecuteDataTable(sb.ToString());
            DataTable dtPhotoWallImageTmp = new DataTable();
            DataTable dtPhotoWallImage = null;

            string sql = string.Empty;
            for (int i = 0; i < dtPhotoWall.Rows.Count; i++)
            {
                sql = @"select photo.ID,Url,`Describe` from tblpartyphotowallimage image
                    left join tblpartyfile file on file.ID=image.FileID
                    left join tblpartyphotowall photo on image.PhotoWallID=photo.ID
                    where PhotoWallID='" + Convert.ToString(dtPhotoWall.Rows[i]["ID"]) + "' order by image.CreateTime desc limit 0,1";
                dtPhotoWallImageTmp = MySqlHelper.ExecuteDataTable(sql);

                if (dtPhotoWallImageTmp.Rows.Count > 0)
                {
                    if (i == 0)
                    {
                        dtPhotoWallImage = dtPhotoWallImageTmp.Copy();
                        dtPhotoWallImage.Clear();
                        dtPhotoWallImage.Rows.Add(dtPhotoWallImageTmp.Rows[0].ItemArray);
                    }
                    else
                    {
                        dtPhotoWallImage.Rows.Add(dtPhotoWallImageTmp.Rows[0].ItemArray);
                    }
                }
            }

            return dtPhotoWallImage;
        }

        /// <summary>
        /// 获取门户首页留影墙列表数据
        /// </summary>
        /// <param name="startIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="category"></param>
        /// <param name="searchKey"></param>
        /// <returns></returns>
        public static DataTable GetPortalPhotoWallList(int startIndex, int pageSize, string category, string searchKey = "")
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            string sql = @"select ID from tblpartyphotowall where STATUS = 4 ";

            if (!string.IsNullOrEmpty(category))
            {
                sql += " and Category = @Category ";
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "Category",
                        Value = string.Format("{0}", category)
                    });
            }

            if (!string.IsNullOrEmpty(searchKey))
            {
                sql += " and Title like @Title ";
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "Title",
                        Value = string.Format("%{0}%", searchKey)
                    });
            }

            sql += " order by CreateTime desc ";

            if (startIndex > 0 && pageSize > 0)
            {
                sql += string.Format(" limit {0},{1} ", (startIndex - 1) * pageSize, pageSize);
            }

            DataTable dtPhotoWall = MySqlHelper.ExecuteDataTable(sql, parameters.ToArray());

            DataTable dtPhotoWallImageTmp = new DataTable();
            DataTable dtPhotoWallImage = null;

            for (int i = 0; i < dtPhotoWall.Rows.Count; i++)
            {

                string itemdd = dtPhotoWall.Rows[i]["ID"].ToString();


                sql = @"select photo.ID,Url,`Describe`,photo.Title,photo.BranchID,photo.CreateTime,UserName from tblpartyphotowallimage image
                    left join tblpartyfile file on file.ID=image.FileID
                    left join tblpartyphotowall photo on image.PhotoWallID=photo.ID
                    left join tblpartyorgmember member on member.LoginID=photo.CreateBy 
                    where PhotoWallID='" + Convert.ToString(dtPhotoWall.Rows[i]["ID"]) + "' order by image.CreateTime desc limit 0,1";
                dtPhotoWallImageTmp = MySqlHelper.ExecuteDataTable(sql);

                if (dtPhotoWallImageTmp.Rows.Count > 0)
                {
                    if (i == 0)
                    {
                        dtPhotoWallImage = dtPhotoWallImageTmp.Copy();
                        dtPhotoWallImage.Clear();
                        dtPhotoWallImage.Rows.Add(dtPhotoWallImageTmp.Rows[0].ItemArray);
                    }
                    else
                        dtPhotoWallImage.Rows.Add(dtPhotoWallImageTmp.Rows[0].ItemArray);
                }
                else
                {
                    if (i == 0)
                    {
                        dtPhotoWallImage = dtPhotoWallImageTmp.Copy();
                        dtPhotoWallImage.Clear();
                    }
                }
            }

            return dtPhotoWallImage;
        }

        public static int GetPhotoWallCount(string category, string title)
        {
            List<MySqlParameter> parameters = new List<MySqlParameter>();

            StringBuilder sb = new StringBuilder();

            sb.Append(@"select count(*) from tblpartyphotowallimage wallimage
left join tblpartyphotowall wall on wall.ID=wallimage.PhotoWallID where 1=1");

            if (!string.IsNullOrEmpty(category))
            {
                sb.Append(" and Category = @Category ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "Category",
                        Value = string.Format("{0}", category)
                    });
            }

            if (!string.IsNullOrEmpty(title))
            {
                sb.Append(" and Title like @Title ");
                parameters.Add(
                    new MySqlParameter()
                    {
                        DbType = DbType.String,
                        ParameterName = "Title",
                        Value = string.Format("%{0}%", title)
                    });
            }

            return Convert.ToInt32(MySqlHelper.ExecuteScalar(sb.ToString(), parameters.ToArray()));
        }
    }
}
